﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using CRMFramework;
using Newtonsoft.Json;

namespace CRMBiz
{
    public static class EmailTemplateHandler
    {

        /// <summary>
        /// function to get column name
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static DataTable getColumnName()
        {
            DataTable dt;
            try
            {
                dt = DBhelper.ExecuteDataset("SELECT * FROM view_getcolumnname").Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to Update email template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static int UpdateEmailTemplate(int EmailId, string TemplateFormat, string emailSubject, int CategoryID, string EmailCompany)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@EmailId", EmailId),
                    DBhelper.SetParameter("@TemplateFormat", TemplateFormat),
                    DBhelper.SetParameter("@emailsubject",emailSubject),
                    DBhelper.SetParameter("@CategoryId", CategoryID),
                    DBhelper.SetParameter("@EmailCompany", EmailCompany),
                };

                string sbSql = "Select * From crm_emailtemplate Where EmailId <> @EmailId And TemplateFormat = @TemplateFormat and emailsubject=@emailsubject and  CategoryId= @CategoryId";
                DataSet ds = DBhelper.ExecuteDataset(sbSql, para);

                if (ds.Tables[0].Rows.Count > 0)
                    intReturnValue = 0;
                else
                {
                    sbSql = "update crm_emailtemplate set TemplateFormat = @TemplateFormat,emailsubject=@emailsubject, CategoryId= @CategoryId,EmailCompany=@EmailCompany where EmailId = @EmailId";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }


        /// <summary>
        /// function to Update email template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static int UpdateEmailTemplateDual(int EmailId, string TemplateFormat, int CategoryID)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@EmailId", EmailId),
                    DBhelper.SetParameter("@TemplateFormat", TemplateFormat),
                    DBhelper.SetParameter("@CategoryId", CategoryID),
                };

                string sbSql = "Select * From crm_emailtemplate Where EmailId <> @EmailId And TemplateFormat = @TemplateFormat";
                DataSet ds = DBhelper.ExecuteDataset(sbSql, para);

                if (ds.Tables[0].Rows.Count > 0)
                    intReturnValue = 0;
                else
                {
                    sbSql = "update crm_emailtemplate set TemplateFormat = @TemplateFormat,CategoryId= @CategoryId where EmailId = @EmailId";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to Insert email template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static int AddEmailTemplate(string EmailTemplate, string TemplateFormat, string emailSubject, int CategoryID, string EmailCompany)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@EmailTemplate", EmailTemplate),
                    DBhelper.SetParameter("@TemplateFormat", TemplateFormat),
                    DBhelper.SetParameter("@emailsubject",emailSubject),
                    DBhelper.SetParameter("@CategoryId", CategoryID),
                    DBhelper.SetParameter("@EmailCompany", EmailCompany),
                };

                string sbSql = "Select * From crm_emailtemplate Where EmailTemplate = @EmailTemplate";
                DataSet ds = DBhelper.ExecuteDataset(sbSql, para);

                if (ds.Tables[0].Rows.Count > 0)
                    intReturnValue = 0;
                else
                {
                    sbSql = "insert into crm_emailtemplate(EmailTemplate,TemplateFormat,emailsubject,CategoryId,EmailCompany) values(@EmailTemplate,@TemplateFormat,@emailsubject,@CategoryId,@EmailCompany)";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to bind email template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static DataTable BindEmailTemplate()
        {
            DataTable dt;
            try
            {
                string sql = "SELECT EmailId,EmailTemplate FROM crm_emailtemplate";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to Delete email template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static bool DeleteEmailTemplate(int EmailId)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@EmailId", EmailId),
                };

                DBhelper.ExecuteNonQuery("Delete from crm_emailtemplate where EmailId=@EmailId", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// function to bind emailId's
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindEmailId(string Company)
        {
            DataTable dt;
            try
            {
                dt = DBhelper.ExecuteDataset(string.Format("SELECT crm_companyemailaccount.emailaccount,crm_companyemailaccount.IsDefault,crm_emailaccount.Password FROM crm_emailaccount INNER JOIN crm_companyemailaccount ON crm_emailaccount.EmailId = crm_companyemailaccount.EmailAccount where crm_companyemailaccount.Company like '%{0}%' ORDER BY crm_companyemailaccount.IsDefault DESC", Company)).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to select password for default
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable SelectPassword()
        {
            DataTable dt;
            try
            {
                string sql = "SELECT Password as setPwd,EmailId FROM crm_emailaccount ORDER BY IsDefault DESC";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to bind Send to Email 
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindEmail(int app_id, int contact_id)
        {
            string str = string.Empty;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            var para = new[] {
                    DBhelper.SetParameter("@p_ContactID", contact_id),
                    DBhelper.SetParameter("@p_app_id", app_id),
                };

            if (app_id == 0 && contact_id != 0)
                sql = new StringBuilder("SELECT CONCAT(FirstName,' ',SurName) AS FullName,Email as  MailID ,'Email1' as Mail,replace(Mobile,' ','') as Mobile FROM crm_contact WHERE ContactID=@p_ContactID and deletedFlag=0")
            .Append(" UNION SELECT CONCAT(FirstName,' ',SurName) AS FullName,secondEmailAddress as MailID ,'Email2' as Mail,replace(Mobile,' ','') as Mobile FROM crm_contact WHERE ContactID=@p_ContactID  and deletedFlag=0 ");
            else
                sql = new StringBuilder("SELECT CONCAT(crm_contact.FirstName,' ',crm_contact.SurName) AS FullName,crm_contact.Email as MailID ,'Email1' as Mail,replace(crm_contact.Mobile,' ','') as Mobile, crm_applicationcontact.NoInApplication as No  FROM crm_applicationcontact")
                    .Append(" INNER JOIN crm_contact ON crm_applicationcontact.ContactID = crm_contact.ContactID WHERE crm_applicationcontact.app_id=@p_app_id AND crm_applicationcontact.deletedFlag=0")
                    .Append(" UNION SELECT CONCAT(crm_contact.FirstName,' ',crm_contact.SurName) AS FullName,crm_contact.secondEmailAddress as MailID ,'Email2' as Mail,replace(crm_contact.Mobile,' ','') as Mobile , crm_applicationcontact.NoInApplication as No  FROM crm_applicationcontact")
                    .Append(" INNER JOIN crm_contact ON crm_applicationcontact.ContactID = crm_contact.ContactID WHERE crm_applicationcontact.app_id=@p_app_id AND crm_applicationcontact.deletedFlag=0")
                    .Append(" Order By No,Mail");
            dt = DBhelper.ExecuteDataset(sql.ToString(), para).Tables[0];


            return dt;
        }



        /// <summary>
        /// function to get email data list
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns>NoticeList</returns>
        public static DataSet GetEmailDataList(int pageIndex, int pageSize, string Company, int CategoryId)
        {

            DataSet ds;
            try
            {
                var sbSql = new StringBuilder("SELECT EmailId,EmailTemplate,EmailCompany,emailSubject,(")
                .AppendFormat("Select count(*) from crm_emailtemplate where EmailCompany LIKE '%{0}%' AND CategoryId={1}) as Total from crm_emailtemplate where EmailCompany LIKE '%{0}%' AND CategoryId={1} order by EmailTemplate ", Company, CategoryId);
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }


        /// <summary>
        /// function to Delete listData
        /// </summary>
        /// <param name="EmailId"></param>   
        /// <returns>true/false</returns>
        public static bool DeleteListData(int EmailId)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@EmailId", EmailId),
                };

                string sbSql = "delete from crm_emailtemplate where EmailId=@EmailId";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
